package yiran.onemonthcard.data;

import yiran.onemonthcard.OneMonthCard;
import yiran.onemonthcard.system.OneMonthCardSystem.OneMonthCardData;
import yiran.onemonthcard.output.Logger;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class DatabaseManager {
    private final String DB_URL;
    private Connection connection;
    private final OneMonthCard plugin;

    public DatabaseManager(OneMonthCard plugin) { // 简化构造函数
        DB_URL = "jdbc:sqlite:" + plugin.getDataFolder().getAbsolutePath() + "/OneMonthCard.db";
        this.plugin = plugin;
        try {
            initializeDatabase();
        } catch (SQLException e) {
            plugin.getLogger().severe("数据库初始化失败，插件将禁用: " + e.getMessage());
            plugin.getServer().getPluginManager().disablePlugin(plugin);
        }
    }

    private void initializeDatabase() throws SQLException {
        connection = DriverManager.getConnection(DB_URL);
        Statement stmt = connection.createStatement();
        stmt.executeUpdate(
                "CREATE TABLE IF NOT EXISTS OneMonthCard_data (" +
                        "player_id TEXT PRIMARY KEY, " +
                        "purchase_date INTEGER, " +
                        "last_claim_date INTEGER, " +
                        "remaining_days INTEGER, " +
                        "is_active INTEGER)"
        );
        plugin.getLogger().info("数据库连接成功");
    }

    public int dateToInt(LocalDate date) {
        return date.getYear() * 10000 + date.getMonthValue() * 100 + date.getDayOfMonth();
    }

    public LocalDate intToDate(int dateInt) {
        int year = dateInt / 10000;
        int month = (dateInt % 10000) / 100;
        int day = dateInt % 100;
        return LocalDate.of(year, month, day);
    }

    private void checkConnection() throws SQLException {
        if (connection == null || connection.isClosed()) {
            connection = DriverManager.getConnection(DB_URL);
        }
    }

    public void saveOneMonthCardData(OneMonthCardData data) {
        String sql = "REPLACE INTO OneMonthCard_data VALUES(?, ?, ?, ?, ?)";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            checkConnection();
            ps.setString(1, data.getPlayerId());
            ps.setInt(2, data.getPurchaseDate() != null ? dateToInt(data.getPurchaseDate()) : 0);
            ps.setInt(3, data.getLastClaimDate() != null ? dateToInt(data.getLastClaimDate()) : 0);
            ps.setInt(4, data.getRemainingDays());
            ps.setInt(5, data.isActive() ? 1 : 0);
            ps.executeUpdate();
        } catch (SQLException e) {
            plugin.getLogger().severe("保存月卡数据失败: " + e.getMessage());
        }
    }

    public OneMonthCardData loadOneMonthCardData(String playerName) {
        String sql = "SELECT * FROM OneMonthCard_data WHERE player_id = ?";

        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, playerName);
            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                OneMonthCardData data = new OneMonthCardData(playerName);
                // 使用新的日期格式
                int purchaseDateInt = rs.getInt("purchase_date");
                data.setPurchaseDate(intToDate(purchaseDateInt));

                int claimDateInt = rs.getInt("last_claim_date");
                data.setLastClaimDate(claimDateInt > 0 ? intToDate(claimDateInt) : null);

                data.setRemainingDays(rs.getInt("remaining_days"));
                data.setActive(rs.getInt("is_active") == 1);
                return data;
            }
        } catch (SQLException e) {
            Logger.error("加载月卡数据失败: " + e.getMessage());
        }
        return null;
    }

    public List<OneMonthCardData> loadAllExpiredCards() {
        List<OneMonthCardData> expiredList = new ArrayList<>();
        String sql = "SELECT * FROM OneMonthCard_data WHERE is_active = 1 AND remaining_days <= 0";

        try (PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            useRs(expiredList, rs);
        } catch (SQLException e) {
            plugin.getLogger().severe("加载过期月卡数据失败: " + e.getMessage());
        }
        return expiredList;
    }

    public void closeConnection() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
                plugin.getLogger().info("数据库连接已关闭");
            }
        } catch (SQLException e) {
            plugin.getLogger().severe("关闭数据库连接失败: " + e.getMessage());
        }
    }

    public List<OneMonthCardData> loadAllOneMonthCardData() {
        List<OneMonthCardData> allData = new ArrayList<>();
        String sql = "SELECT * FROM OneMonthCard_data";

        try (PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            useRs(allData, rs);
        } catch (SQLException e) {
            plugin.getLogger().severe("加载所有月卡数据失败: " + e.getMessage());
        }
        return allData;
    }

    private void useRs(List<OneMonthCardData> allData, ResultSet rs) throws SQLException {
        while (rs.next()) {
            String playerId = rs.getString("player_id");
            OneMonthCardData data = new OneMonthCardData(playerId);

            int purchaseDateInt = rs.getInt("purchase_date");
            data.setPurchaseDate(purchaseDateInt > 0 ? intToDate(purchaseDateInt) : null);

            int claimDateInt = rs.getInt("last_claim_date");
            data.setLastClaimDate(claimDateInt > 0 ? intToDate(claimDateInt) : null);

            data.setRemainingDays(rs.getInt("remaining_days"));
            data.setActive(rs.getInt("is_active") == 1);

            allData.add(data);
        }
    }

    public List<String> getAllPlayerNames() {
        List<String> playerNames = new ArrayList<>();
        String sql = "SELECT player_id FROM OneMonthCard_data";

        try (PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                playerNames.add(rs.getString("player_id"));
            }
        } catch (SQLException e) {
            plugin.getLogger().severe("获取玩家列表失败: " + e.getMessage());
        }
        return playerNames;
    }
}
